package com.boesch.electroOffice.dataaccess.daoImpl;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.boesch.electroOffice.dataaccess.common.IllegalDataAccessException;
import com.boesch.electroOffice.dataaccess.common.ReturnCodesDataaccess;
import com.boesch.electroOffice.dataaccess.dao.IdentifierDao;
import com.boesch.electroOffice.dataaccess.dao.ToolsDao;
import com.boesch.electroOffice.dataaccess.mapper.ToolsMapper;
import com.boesch.electroOffice.model.electroOffice.Tool;
import com.boesch.electroOffice.utils.DateUtils;
import com.boesch.electroOffice.utils.UserUtils;
import com.liferay.portal.kernel.log.Log;
import com.liferay.portal.kernel.log.LogFactoryUtil;
import com.liferay.portal.model.User;

/**
 * This class executes the sql statements on the database for class Tools.
 * If a Exception is thrown, 
 * it will be unpacked to a illegalDataAccessException 
 * including a return code which can be shown to the user.
 * 
 * @author Ralph Koster
 * 
 */
public class ToolsDaoImpl implements ToolsDao, Serializable {

	private static final long serialVersionUID = 7629396013148332284L;
	private static final Log log = LogFactoryUtil.getLog(ToolsDaoImpl.class);

	@Autowired
	private IdentifierDao identifierDao;
	@Autowired
	private JdbcTemplate eoJdbcTemplate;

	@Value("${electroOffice.queryCreateTool}")
	private String queryCreateTool;
	@Value("${electroOffice.queryReadToolByToolId}")
	private String queryReadToolByToolId;
	@Value("${electroOffice.queryReadToolByInternalNumber}")
	private String queryReadToolByInternalNumber;
	@Value("${electroOffice.queryReadToolByEntityAndIdentifier}")
	private String queryReadToolByEntityAndIdentifier;
	@Value("${electroOffice.queryReadTools}")
	private String queryReadTools;
	@Value("${electroOffice.queryReadToolsByEntity}")
	private String queryReadToolsByEntity;
	@Value("${electroOffice.queryUpdateTool}")
	private String queryUpdateTool;
	@Value("${electroOffice.queryDeleteToolByToolId}")
	private String queryDeleteToolByToolId;
	@Value("${electroOffice.queryDeleteToolByInternalNumber}")
	private String queryDeleteToolByInternalNumber;
	@Value("${electroOffice.queryDeleteToolByEntityAndIdentifier}")
	private String queryDeleteToolByEntityAndIdentifier;

	/**
	 * {@inheritDoc}
	 */
	public String createTool(final Tool tool) throws IllegalDataAccessException {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		final User currentUser = UserUtils.getCurrentUser();
		try {
			eoJdbcTemplate.update(new PreparedStatementCreator() {
				public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
					PreparedStatement ps = connection.prepareStatement(queryCreateTool, Statement.RETURN_GENERATED_KEYS);
					ps.setString(1, tool.getName());
					ps.setString(2, tool.getType());
					ps.setString(3, tool.getDescription());
					ps.setString(4, tool.getManufacturer());
					ps.setString(5, tool.getInternalNumber());
					ps.setInt(6, tool.getAppointmentId());
					ps.setLong(7, tool.getLrUserId());
					ps.setInt(8, null != tool.getToolIdentifier() ? tool.getToolIdentifier().getId() : null);
					ps.setString(9, currentUser.getFullName());
					ps.setTimestamp(10, DateUtils.getCurrentTimestamp());
					ps.setInt(11, 1);
					return ps;
				}
			}, keyHolder);
			log.debug("Inserted tool with id: " + keyHolder.getKey());
			return ReturnCodesDataaccess.CREATED.getValue();
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.CREATION_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public Tool readToolByToolId(int toolId) throws IllegalDataAccessException {
		Tool tool = null;
		List<Tool> tools = eoJdbcTemplate.query(queryReadToolByToolId, new Object[] { toolId }, new ToolsMapper(identifierDao));
		if (null != tools && tools.size() == 1) {
			tool = tools.get(0);
			return tool;
		} else {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.OBJECT_NOT_FOUND_ERROR.getValue());
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public Tool readToolByInternalNumber(String internalNumber) throws IllegalDataAccessException {
		Tool tool = null;
		List<Tool> tools = eoJdbcTemplate.query(queryReadToolByInternalNumber, new Object[] { internalNumber }, new ToolsMapper(identifierDao));
		if (null != tools && tools.size() == 1) {
			tool = tools.get(0);
			return tool;
		} else {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.OBJECT_NOT_FOUND_ERROR.getValue());
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public Tool readToolByEntityAndIdentifier(String entity, String identifier) throws IllegalDataAccessException {
		Tool tool = null;
		List<Tool> tools = eoJdbcTemplate.query(queryReadToolByEntityAndIdentifier, new Object[] { entity, identifier }, new ToolsMapper(identifierDao));
		if (null != tools && tools.size() == 1) {
			tool = tools.get(0);
			return tool;
		} else {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.OBJECT_NOT_FOUND_ERROR.getValue());
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public List<Tool> readTools() throws IllegalDataAccessException {
		List<Tool> tools = new ArrayList<Tool>();
		try {
			tools = eoJdbcTemplate.query(queryReadTools, new Object[] {}, new ToolsMapper(identifierDao));
			return tools;
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.NO_OBJECTS_FOUND_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public List<Tool> readToolsByEntity(String entity) throws IllegalDataAccessException {
		List<Tool> tools = new ArrayList<Tool>();
		try {
			tools = eoJdbcTemplate.query(queryReadToolsByEntity, new Object[] { entity }, new ToolsMapper(identifierDao));
			return tools;
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.NO_OBJECTS_FOUND_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public String updateTool(final Tool tool) throws IllegalDataAccessException {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		final User currentUser = UserUtils.getCurrentUser();
		try {
			eoJdbcTemplate.update(new PreparedStatementCreator() {
				public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
					PreparedStatement ps = connection.prepareStatement(queryUpdateTool, Statement.RETURN_GENERATED_KEYS);
					ps.setString(1, tool.getName());
					ps.setString(2, tool.getType());
					ps.setString(3, tool.getDescription());
					ps.setString(4, tool.getManufacturer());
					ps.setString(5, tool.getInternalNumber());
					ps.setInt(6, tool.getAppointmentId());
					ps.setLong(7, tool.getLrUserId());
					ps.setInt(8, null != tool.getToolIdentifier() ? tool.getToolIdentifier().getId() : null);
					ps.setString(9, currentUser.getFullName());
					ps.setInt(10, tool.getVersion() + 1);
					return ps;
				}
			}, keyHolder);
			log.debug("Updated tool with id: " + keyHolder.getKey());
			return ReturnCodesDataaccess.UPDATED.getValue();
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.UPDATE_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public String deleteToolByToolId(int toolId) throws IllegalDataAccessException {
		try {
			eoJdbcTemplate.update(queryDeleteToolByToolId, toolId);
			log.debug("Deleted tool with toolId: " + toolId);
			return ReturnCodesDataaccess.DELETED.getValue();
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.DELETION_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public String deleteToolByInternalNumber(String internalNumber) throws IllegalDataAccessException {
		try {
			eoJdbcTemplate.update(queryDeleteToolByInternalNumber, internalNumber);
			log.debug("Deleted tool with internalNumber: " + internalNumber);
			return ReturnCodesDataaccess.DELETED.getValue();
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.DELETION_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public String deleteToolByEntityAndIdentifier(String entity, String identifier) throws IllegalDataAccessException {
		try {
			eoJdbcTemplate.update(queryDeleteToolByEntityAndIdentifier, entity, identifier);
			log.debug("Deleted tool with entity: " + entity + " and identifier: " + identifier);
			return ReturnCodesDataaccess.DELETED.getValue();
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.DELETION_ERROR.getValue(), e);
		}
	}
}
